﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLiNhaSachDTO;

namespace QuanLiNhaSachDAO
{
    public class SachDAO
    {
        public static DataTable GetById(int id)
        {
            string sql = "select id, Ten, GiaMua, GiaBan, MaNhaCungCap, SoLuong from SACH where id = " + id;
            SqlDataAdapter sda = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
        public static DataTable SelectAllForGrid()
        {
            string sql = "SELECT Results.id, Results.Ten, Results.GiaMua, Results.GiaBan, NHACUNGCAP.Ten AS NhaCungCap, Results.SoLuong, REPLACE(REPLACE(REPLACE((SELECT CAST(NHAXUATBAN.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHNHAXUATBAN INNER JOIN NHAXUATBAN ON SACHNHAXUATBAN.MaNhaXuatBan = NHAXUATBAN.id WHERE (SACHNHAXUATBAN.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS NhaXuatBan, REPLACE(REPLACE(REPLACE((SELECT CAST(THELOAI.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHTHELOAI INNER JOIN THELOAI ON SACHTHELOAI.MaTheLoai = THELOAI.id WHERE (SACHTHELOAI.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS TheLoai, REPLACE(REPLACE(REPLACE((SELECT CAST(TACGIA.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHTACGIA INNER JOIN TACGIA ON SACHTACGIA.MaTacGia = TACGIA.id WHERE (SACHTACGIA.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS TacGia FROM SACH AS Results INNER JOIN NHACUNGCAP ON Results.MaNhaCungCap = NHACUNGCAP.id GROUP BY Results.id, Results.Ten, Results.GiaMua, Results.GiaBan, NHACUNGCAP.Ten, Results.SoLuong";
            SqlDataAdapter sda = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
            
        }
        public static DataTable SelectAll()
        {
            string sql = "select * from SACH";
            SqlDataAdapter sda = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
        public static int Insert(Sach q)
        {
            string sql = "insert into SACH(Ten, GiaMua, GiaBan,SoLuong,MaNhaCungCap) OUTPUT INSERTED.id " +
                        "values(@Ten,@GiaMua,@GiaBan,@SoLuong,@MaNhaCungCap)";
            SqlCommand scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.Parameters.AddWithValue("@Ten", q.TenSach);
            scmd.Parameters.AddWithValue("@GiaMua", q.GiaMua);
            scmd.Parameters.AddWithValue("@GiaBan", q.GiaBan);
            scmd.Parameters.AddWithValue("@SoLuong", q.SoLuong);
            scmd.Parameters.AddWithValue("@MaNhaCungCap", q.NhaCungCap);
            return (int)scmd.ExecuteScalar();
        }
        public static void ResetReference(int MaSach)
        {
            string sql = "DELETE FROM SACHTHELOAI WHERE (MaSach = "+ MaSach +")";
            SqlCommand scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.ExecuteNonQuery();

            sql = "DELETE FROM SACHTACGIA WHERE (MaSach = " + MaSach + ")";
            scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.ExecuteNonQuery();

            sql = "DELETE FROM SACHNHAXUATBAN WHERE (MaSach = " + MaSach + ")";
            scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.ExecuteNonQuery();
        }
        public static void InsertSachTheLoai(int MaSach, int MaTheLoai)
        {
            string sql = "insert into SACHTHELOAI(MaSach, MaTheLoai) " +
                        "values(@MaSach, @MaTheLoai)";
            SqlCommand scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.Parameters.AddWithValue("@MaSach", MaSach);
            scmd.Parameters.AddWithValue("@MaTheLoai", MaTheLoai);
            scmd.ExecuteNonQuery();
        }
        public static void InsertSachTacGia(int MaSach, int MaTacGia)
        {
            string sql = "insert into SACHTACGIA(MaSach, MaTacGia) " +
                        "values(@MaSach, @MaTacGia)";
            SqlCommand scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.Parameters.AddWithValue("@MaSach", MaSach);
            scmd.Parameters.AddWithValue("@MaTacGia", MaTacGia);
            scmd.ExecuteNonQuery();
        }
        public static void InsertSachNhaXuatBan(int MaSach, int MaNhaXuatBan)
        {
            string sql = "insert into SACHNHAXUATBAN(MaSach, MaNhaXuatBan) " +
                        "values(@MaSach, @MaNhaXuatBan)";
            SqlCommand scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.Parameters.AddWithValue("@MaSach", MaSach);
            scmd.Parameters.AddWithValue("@MaNhaXuatBan", MaNhaXuatBan);
            scmd.ExecuteNonQuery();
        }
        public static void Update(Sach q)
        {
            string sql = "update SACH set Ten=@Ten, GiaMua=@GiaMua, GiaBan=@GiaBan, SoLuong=@SoLuong," +
                        "MaNhaCungCap=@MaNhaCungCap where id=@id";
            SqlCommand scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.Parameters.AddWithValue("@Ten", q.TenSach);
            scmd.Parameters.AddWithValue("@GiaMua", q.GiaMua);
            scmd.Parameters.AddWithValue("@GiaBan", q.GiaBan);
            scmd.Parameters.AddWithValue("@SoLuong", q.SoLuong);
            scmd.Parameters.AddWithValue("@MaNhaCungCap", q.NhaCungCap);
            scmd.Parameters.AddWithValue("@id", q.Id);
            scmd.ExecuteNonQuery();
        }
        public static void Delete(Sach q)
        {
            XoaSachTacGia(q.Id);
            XoaSachNhaXuatBan(q.Id);
            XoaSachTheLoai(q.Id);
            string sql = "delete from SACH where id=@id";
            SqlCommand scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.Parameters.AddWithValue("@id", q.Id);
            scmd.ExecuteNonQuery();
        }

        private static void XoaSachTheLoai(string id)
        {
            string sql = "delete from SACHTHELOAI where masach=@id";
            SqlCommand scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.Parameters.AddWithValue("@id", id);
            scmd.ExecuteNonQuery();            
        }

        private static void XoaSachNhaXuatBan(string id)
        {
            string sql = "delete from SACHNHAXUATBAN where masach=@id";
            SqlCommand scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.Parameters.AddWithValue("@id", id);
            scmd.ExecuteNonQuery();            
        }

        private static void XoaSachTacGia(string id)
        {
            string sql = "delete from SACHTACGIA where masach=@id";
            SqlCommand scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.Parameters.AddWithValue("@id", id);
            scmd.ExecuteNonQuery();            
        }

        public static bool kiemTraXoa(string id)
        {
            string sql = "select id from CHITIETHOADON where masach = " + id;
            SqlDataAdapter sda = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            sda.Fill(dt);

            sql = "select id from CHITIETPHIEUNHAP where masach = " + id;
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt2 = new DataTable();
            da.Fill(dt2);

            return ((dt.Rows.Count == 0) && (dt2.Rows.Count == 0));
        }
    }
}
